import subprocess
import xml.etree.ElementTree as ET
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from sqlalchemy import create_engine, text
import re
import time
from typing import List, Dict

def send_email(subject: str, body: str, config: ET.Element) -> None:
    """发送通知邮件"""
    email_server = config.find('.//email_server_address')
    host = email_server.get('host')
    port = int(email_server.get('port'))
    tls = email_server.get('tls').lower() == 'true'

    sender = config.find('.//sender')
    sender_email = sender.get('email')
    sender_password = sender.get('password')

    receivers = config.findall('.//receiver')
    receiver_emails = [receiver.get('email') for receiver in receivers]

    # 构建邮件
    msg = MIMEMultipart()
    msg['From'] = sender_email
    msg['To'] = ", ".join(receiver_emails)
    msg['Subject'] = subject
    msg.attach(MIMEText(body, 'html'))

    try:
        with smtplib.SMTP(host, port) as server:
            if tls:
                server.starttls()
            server.login(sender_email, sender_password)
            server.sendmail(sender_email, receiver_emails, msg.as_string())
            print("通知邮件已发送!")
    except Exception as e:
        print(f"邮件发送失败: {e}")

def replace_in_patch(content: str, replacements: Dict[str, str]) -> str:
    """使用正则表达式替换 SQL Patch 文件内容中的关键字段"""
    for pattern, replacement in replacements.items():
        content = re.sub(pattern, replacement, content)
    return content

def apply_patches(to_db: str, to_user: str, to_password: str, patch_files: List[str], replacements: Dict[str, str]) -> None:
    """依次应用 SQL Patch 来创建表或视图等"""
    connection_string = f'oracle+cx_oracle://{to_user}:{to_password}@{to_db}'
    
    try:
        engine = create_engine(connection_string)

        with engine.connect() as connection:
            for patch_file in patch_files:
                with open(patch_file, 'r') as file:
                    sql_content = file.read()
                    # 在刷入前进行正则替换
                    sql_content = replace_in_patch(sql_content, replacements)
                    connection.execute(text(sql_content))
                print(f"SQL Patch {patch_file} 应用成功")
    except Exception as e:
        print(f"应用 SQL Patch 时出错: {e}")
        raise  # 抛出异常以便上层函数捕获

def create_dblinks(to_db: str, to_user: str, to_password: str, dblink_configs: List[ET.Element]) -> None:
    """创建 DBLink"""
    connection_string = f'oracle+cx_oracle://{to_user}:{to_password}@{to_db}'
    
    try:
        engine = create_engine(connection_string)

        with engine.connect() as connection:
            for dblink_config in dblink_configs:
                dblink_name = dblink_config.get('name')
                dblink_user = dblink_config.get('user')
                dblink_password = dblink_config.get('password')
                connect_string = to_db  # 假设dblink指向的是相同的to_db，若不同请修改为相应连接串

                create_dblink_sql = f"""
                CREATE DATABASE LINK {dblink_name}
                CONNECT TO {dblink_user} IDENTIFIED BY {dblink_password}
                USING '{connect_string}';
                """
                connection.execute(text(create_dblink_sql))
                print(f"创建数据库链接 {dblink_name} 成功")

    except Exception as e:
        print(f"创建数据库链接时出错: {e}")
        raise  # 抛出异常以便上层函数捕获
def wait_for_status(engine: create_engine, wait_status: int) -> None:
    """等待数据库中的某个状态达到指定值"""
    try:
        with engine.connect() as connection:
            while True:
                result = connection.execute(text("SELECT status_column FROM status_table"))  # 替换为实际的查询
                current_status = result.scalar()
                print(f"当前状态: {current_status}，等待状态: {wait_status}")

                if current_status == wait_status:
                    print("状态匹配，继续处理...")
                    break
                time.sleep(10)  # 每10秒检查一次
    except Exception as e:
        print(f"等待状态时出错: {e}")
        raise

def run_subprocess(command: List[str]) -> int:
    """运行子进程并处理输出，确保同步操作和其他脚本顺利执行"""
    try:
        process = subprocess.Popen(command, stdout=subprocess.PIPE, stderr=subprocess.STDOUT, text=True)

        # 实时读取输出
        while True:
            output = process.stdout.readline()
            if output:
                print(output.strip())
            elif process.poll() is not None:
                break

        # 等待进程结束
        process.wait()
        print(f"命令执行完成，状态码: {process.returncode}")

        return process.returncode

    except Exception as e:
        print(f"执行命令时发生错误: {e}")
        raise

def run_db_sync(config_file: str) -> None:
    """执行数据库同步和比较流程"""
    try:
        # 解析 XML 配置文件
        tree = ET.parse(config_file)
        root = tree.getroot()

        # 获取同步配置
        sync_config = root.find('.//sync')
        wait_status_sync = int(sync_config.get('wait_status'))
        
        from_db = sync_config.find('.//from_database').text
        from_user = sync_config.find('.//from_database').get('user')
        from_password = sync_config.find('.//from_database').get('password')
        
        to_db = sync_config.find('.//to_database').text
        to_user = sync_config.find('.//to_database').get('user')
        to_password = sync_config.find('.//to_database').get('password')

        # 获取所有 Patch 文件的路径
        patch_files = [patch.text for patch in sync_config.findall('.//patch')]
        
        # 获取 DBLink 配置
        dblink_configs = sync_config.findall('.//dblink')

        # 在同步之前检查from_database状态
        print(f"检查from_database状态，等待状态达到 {wait_status_sync}...")
        engine_from = create_engine(f'oracle+cx_oracle://{from_user}:{from_password}@{from_db}')
        wait_for_status(engine_from, wait_status_sync)

        # 应用同步的patch文件
        print("应用同步的patch文件...")
        apply_patches(to_db, to_user, to_password, patch_files, {})

        # 创建同步的dblink
        print("创建同步的数据库链接...")
        create_dblinks(to_db, to_user, to_password, dblink_configs)

        # 执行close.py
        print("执行close.py...")
        if run_subprocess(["python3", "close.py"]) != 0:
            raise Exception("close.py 执行失败")
        # 第二次数据同步
        print("开始第二次数据同步...")
        db_sync_command = [
            "python3", "db_sync_script.py",  # 替换为实际的同步脚本
            "--from-db", from_db,
            "--from-user", from_user,
            "--from-password", from_password,
            "--to-db", to_db,
            "--to-user", to_user,
            "--to-password", to_password
        ]
        if run_subprocess(db_sync_command) != 0:
            raise Exception("第二次数据同步失败")

        # 执行clear.py
        print("执行clear.py...")
        if run_subprocess(["python3", "clear.py"]) != 0:
            raise Exception("clear.py 执行失败")

        # 获取cmp配置
        cmp_config = root.find('.//cmp')
        wait_status_cmp = int(cmp_config.get('wait_status'))
        
        benchmark_db = cmp_config.find('.//benchmark_database').text
        benchmark_user = cmp_config.find('.//benchmark_database').get('user')
        benchmark_password = cmp_config.find('.//benchmark_database').get('password')
        
        verification_db = cmp_config.find('.//verification_database').text
        verification_user = cmp_config.find('.//verification_database').get('user')
        verification_password = cmp_config.find('.//verification_database').get('password')
        
        cmp_patches = [patch.text for patch in cmp_config.findall('.//patch')]
        cmp_dblinks = cmp_config.findall('.//dblink')

        # 在对比之前检查verification_database状态
        print(f"检查verification_database状态，等待状态达到 {wait_status_cmp}...")
        engine_verification = create_engine(f'oracle+cx_oracle://{verification_user}:{verification_password}@{verification_db}')
        wait_for_status(engine_verification, wait_status_cmp)

        # 应用cmp的patch文件
        print("应用cmp的patch文件...")
        apply_patches(benchmark_db, benchmark_user, benchmark_password, cmp_patches, {})

        # 创建cmp的dblink
        print("创建cmp的数据库链接...")
        create_dblinks(benchmark_db, benchmark_user, benchmark_password, cmp_dblinks)

        # 执行compare.py
        print("执行compare.py...")
        if run_subprocess(["python3", "compare.py"]) != 0:
            raise Exception("compare.py 执行失败")

        # 成功完成后发送通知
        send_email("数据库同步和处理成功", "<p>整个流程已成功完成。</p>", root)

    except Exception as e:
        print(f"执行过程中发生错误: {e}")
        send_email("流程执行失败", f"<p>执行过程中发生错误: {e}</p>", root)
        raise  # 终止整个流程

def generate_email_body(link: str, table_data: list[dict[str, str]]) -> str:
    """生成带有列合并的邮件HTML内容，包括链接和表格"""
    # 构建表格的HTML部分，添加边框和样式
    table_html = """
    <table border="1" cellpadding="5" cellspacing="0" style="border-collapse: collapse; width: 100%;">
        <thead>
            <tr style="background-color: #f2f2f2;">
                <th style="border: 1px solid black;">列1</th>
                <th style="border: 1px solid black;">列2</th>
                <th style="border: 1px solid black;">列3</th>
                <th style="border: 1px solid black;">列4</th>
                <th style="border: 1px solid black;">列5</th>
            </tr>
        </thead>
        <tbody>
    """
    
    # 添加每一行的数据
    for index, row in enumerate(table_data):
        table_html += "<tr>"
        table_html += f"<td style='border: 1px solid black;'>{row.get('col1', '')}</td>"
        table_html += f"<td style='border: 1px solid black;'>{row.get('col2', '')}</td>"
        
        # 对第二行进行后三列合并处理
        if index == 1:
            merged_data = f"{row.get('col3', '')} {row.get('col4', '')} {row.get('col5', '')}"
            table_html += f"<td colspan='3' style='border: 1px solid black;'>{merged_data}</td>"
        else:
            # 正常处理其他行
            table_html += f"<td style='border: 1px solid black;'>{row.get('col3', '')}</td>"
            table_html += f"<td style='border: 1px solid black;'>{row.get('col4', '')}</td>"
            table_html += f"<td style='border: 1px solid black;'>{row.get('col5', '')}</td>"
        
        table_html += "</tr>"
    
    table_html += """
        </tbody>
    </table>
    """

    # 构建完整的邮件内容
    email_body = f"""
    <html>
        <body>
            <h2>您好！</h2>
            <p>请查看以下链接：</p>
            <p><a href="{link}">{link}</a></p>
            <p>以下是相关数据：</p>
            {table_html}
            <p>谢谢！</p>
        </body>
    </html>
    """    
    return email_body

# 示例用法
if __name__ == "__main__":
    config_file = 'config.xml'  # 替换为实际的配置文件路径
    try:
        run_db_sync(config_file)
    except Exception as e:
        print("同步流程已终止。")
